﻿ USE [EventScavenger]
GO
/****** Object:  View [dbo].[EventLogHistoryEx]    Script Date: 03/11/2010 09:10:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[EventLogHistoryEx]
AS
SELECT     dbo.EventLogHistory.HistoryID, dbo.MachineLogs.Machine, dbo.MachineLogs.LogName, dbo.EventLogHistory.Year, dbo.EventLogHistory.Month, 
                      dbo.EventLogHistory.Day, dbo.EventLogHistory.Hour, dbo.EventSources.Source, dbo.EventLogHistory.EventTypeName, dbo.EventLogHistory.Records,
                       dbo.EventLogHistory.EventID
FROM         dbo.EventLogHistory INNER JOIN
                      dbo.MachineLogs ON dbo.EventLogHistory.MachineLogID = dbo.MachineLogs.MachineLogID INNER JOIN
                      dbo.EventSources ON dbo.EventLogHistory.SourceID = dbo.EventSources.SourceID
GO
EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPane1', @value=N'[0E232FF0-B466-11cf-A24F-00AA00A3EFFF, 1.00]
Begin DesignProperties = 
   Begin PaneConfigurations = 
      Begin PaneConfiguration = 0
         NumPanes = 4
         Configuration = "(H (1[40] 4[20] 2[20] 3) )"
      End
      Begin PaneConfiguration = 1
         NumPanes = 3
         Configuration = "(H (1 [50] 4 [25] 3))"
      End
      Begin PaneConfiguration = 2
         NumPanes = 3
         Configuration = "(H (1 [50] 2 [25] 3))"
      End
      Begin PaneConfiguration = 3
         NumPanes = 3
         Configuration = "(H (4 [30] 2 [40] 3))"
      End
      Begin PaneConfiguration = 4
         NumPanes = 2
         Configuration = "(H (1 [56] 3))"
      End
      Begin PaneConfiguration = 5
         NumPanes = 2
         Configuration = "(H (2 [66] 3))"
      End
      Begin PaneConfiguration = 6
         NumPanes = 2
         Configuration = "(H (4 [50] 3))"
      End
      Begin PaneConfiguration = 7
         NumPanes = 1
         Configuration = "(V (3))"
      End
      Begin PaneConfiguration = 8
         NumPanes = 3
         Configuration = "(H (1[56] 4[18] 2) )"
      End
      Begin PaneConfiguration = 9
         NumPanes = 2
         Configuration = "(H (1 [75] 4))"
      End
      Begin PaneConfiguration = 10
         NumPanes = 2
         Configuration = "(H (1[66] 2) )"
      End
      Begin PaneConfiguration = 11
         NumPanes = 2
         Configuration = "(H (4 [60] 2))"
      End
      Begin PaneConfiguration = 12
         NumPanes = 1
         Configuration = "(H (1) )"
      End
      Begin PaneConfiguration = 13
         NumPanes = 1
         Configuration = "(V (4))"
      End
      Begin PaneConfiguration = 14
         NumPanes = 1
         Configuration = "(V (2))"
      End
      ActivePaneConfig = 0
   End
   Begin DiagramPane = 
      Begin Origin = 
         Top = 0
         Left = 0
      End
      Begin Tables = 
         Begin Table = "EventLogHistory"
            Begin Extent = 
               Top = 6
               Left = 38
               Bottom = 235
               Right = 198
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "MachineLogs"
            Begin Extent = 
               Top = 8
               Left = 275
               Bottom = 154
               Right = 441
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "EventSources"
            Begin Extent = 
               Top = 157
               Left = 282
               Bottom = 242
               Right = 434
            End
            DisplayFlags = 280
            TopColumn = 0
         End
      End
   End
   Begin SQLPane = 
   End
   Begin DataPane = 
      Begin ParameterDefaults = ""
      End
   End
   Begin CriteriaPane = 
      Begin ColumnWidths = 11
         Column = 1440
         Alias = 900
         Table = 1170
         Output = 720
         Append = 1400
         NewValue = 1170
         SortType = 1350
         SortOrder = 1410
         GroupBy = 1350
         Filter = 1350
         Or = 1350
         Or = 1350
         Or = 1350
      End
   End
End
' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'EventLogHistoryEx'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPaneCount', @value=1 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'EventLogHistoryEx'
GO
/****** Object:  View [dbo].[EventLogByHour]    Script Date: 03/11/2010 09:10:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[EventLogByHour]
  
AS
SELECT     YEAR(TimeGenerated) AS Year, MONTH(TimeGenerated) AS Month, DAY(TimeGenerated) AS Day, DATEPART(hh, TimeGenerated) AS Hour, EventID, 
                      SourceID, EventTypeName, COUNT_BIG(EntryID) AS Records, CONVERT(datetime, CAST(YEAR(TimeGenerated) AS char(4)) 
                      + '-' + CAST(MONTH(TimeGenerated) AS char(2)) + '-' + CAST(DAY(TimeGenerated) AS char(2)) + ' ' + CAST(DATEPART(hh, TimeGenerated) AS char(2)) 
                      + ':00') AS Date, MachineLogID
FROM         dbo.EventLogEntries
GROUP BY YEAR(TimeGenerated), MONTH(TimeGenerated), DAY(TimeGenerated), DATEPART(hh, TimeGenerated), SourceID, EventTypeName, EventID, 
                      MachineLogID
GO
EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPane1', @value=N'[0E232FF0-B466-11cf-A24F-00AA00A3EFFF, 1.00]
Begin DesignProperties = 
   Begin PaneConfigurations = 
      Begin PaneConfiguration = 0
         NumPanes = 4
         Configuration = "(H (1[41] 4[25] 2[16] 3) )"
      End
      Begin PaneConfiguration = 1
         NumPanes = 3
         Configuration = "(H (1 [50] 4 [25] 3))"
      End
      Begin PaneConfiguration = 2
         NumPanes = 3
         Configuration = "(H (1 [50] 2 [25] 3))"
      End
      Begin PaneConfiguration = 3
         NumPanes = 3
         Configuration = "(H (4 [30] 2 [40] 3))"
      End
      Begin PaneConfiguration = 4
         NumPanes = 2
         Configuration = "(H (1 [56] 3))"
      End
      Begin PaneConfiguration = 5
         NumPanes = 2
         Configuration = "(H (2 [66] 3))"
      End
      Begin PaneConfiguration = 6
         NumPanes = 2
         Configuration = "(H (4 [50] 3))"
      End
      Begin PaneConfiguration = 7
         NumPanes = 1
         Configuration = "(V (3))"
      End
      Begin PaneConfiguration = 8
         NumPanes = 3
         Configuration = "(H (1[56] 4[18] 2) )"
      End
      Begin PaneConfiguration = 9
         NumPanes = 2
         Configuration = "(H (1 [75] 4))"
      End
      Begin PaneConfiguration = 10
         NumPanes = 2
         Configuration = "(H (1[66] 2) )"
      End
      Begin PaneConfiguration = 11
         NumPanes = 2
         Configuration = "(H (4 [60] 2))"
      End
      Begin PaneConfiguration = 12
         NumPanes = 1
         Configuration = "(H (1) )"
      End
      Begin PaneConfiguration = 13
         NumPanes = 1
         Configuration = "(V (4))"
      End
      Begin PaneConfiguration = 14
         NumPanes = 1
         Configuration = "(V (2))"
      End
      ActivePaneConfig = 0
   End
   Begin DiagramPane = 
      Begin Origin = 
         Top = 0
         Left = 0
      End
      Begin Tables = 
         Begin Table = "EventLogEntries"
            Begin Extent = 
               Top = 6
               Left = 38
               Bottom = 245
               Right = 230
            End
            DisplayFlags = 280
            TopColumn = 0
         End
      End
   End
   Begin SQLPane = 
   End
   Begin DataPane = 
      Begin ParameterDefaults = ""
      End
      Begin ColumnWidths = 9
         Width = 284
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
      End
   End
   Begin CriteriaPane = 
      Begin ColumnWidths = 12
         Column = 2685
         Alias = 900
         Table = 2760
         Output = 720
         Append = 1400
         NewValue = 1170
         SortType = 1350
         SortOrder = 1410
         GroupBy = 1350
         Filter = 1350
         Or = 1350
         Or = 1350
         Or = 1350
      End
   End
End
' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'EventLogByHour'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPaneCount', @value=1 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'EventLogByHour'
GO
/****** Object:  View [dbo].[EventLog]    Script Date: 03/11/2010 09:10:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[EventLog]
AS
SELECT     dbo.EventLogEntries.EntryID, dbo.EventLogEntries.MachineLogID, dbo.MachineLogs.Machine AS ComputerName, dbo.MachineLogs.LogName AS EventLog, 
                      dbo.EventLogEntries.RecordNumber, dbo.EventLogEntries.EventID, dbo.EventLogEntries.TimeGenerated, dbo.EventSources.Source AS SourceName, 
                      dbo.EventLogEntries.EventTypeName, dbo.EventLogEntries.Message, dbo.EventCategories.Category, dbo.EventLogEntries.SourceID, 
                      dbo.EventLogEntries.CategoryID, dbo.UserNames.UserName, dbo.MachineLogs.Enabled AS EnabledMachine, dbo.EventLogEntries.Repeat
FROM         dbo.EventLogEntries INNER JOIN
                      dbo.MachineLogs ON dbo.EventLogEntries.MachineLogID = dbo.MachineLogs.MachineLogID INNER JOIN
                      dbo.EventSources ON dbo.EventLogEntries.SourceID = dbo.EventSources.SourceID LEFT OUTER JOIN
                      dbo.UserNames ON dbo.EventLogEntries.UserNameID = dbo.UserNames.UserNameID LEFT OUTER JOIN
                      dbo.EventCategories ON dbo.EventLogEntries.CategoryID = dbo.EventCategories.CategoryID
GO
EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPane1', @value=N'[0E232FF0-B466-11cf-A24F-00AA00A3EFFF, 1.00]
Begin DesignProperties = 
   Begin PaneConfigurations = 
      Begin PaneConfiguration = 0
         NumPanes = 4
         Configuration = "(H (1[41] 4[22] 2[19] 3) )"
      End
      Begin PaneConfiguration = 1
         NumPanes = 3
         Configuration = "(H (1 [50] 4 [25] 3))"
      End
      Begin PaneConfiguration = 2
         NumPanes = 3
         Configuration = "(H (1 [50] 2 [25] 3))"
      End
      Begin PaneConfiguration = 3
         NumPanes = 3
         Configuration = "(H (4 [30] 2 [40] 3))"
      End
      Begin PaneConfiguration = 4
         NumPanes = 2
         Configuration = "(H (1 [56] 3))"
      End
      Begin PaneConfiguration = 5
         NumPanes = 2
         Configuration = "(H (2 [66] 3))"
      End
      Begin PaneConfiguration = 6
         NumPanes = 2
         Configuration = "(H (4 [50] 3))"
      End
      Begin PaneConfiguration = 7
         NumPanes = 1
         Configuration = "(V (3))"
      End
      Begin PaneConfiguration = 8
         NumPanes = 3
         Configuration = "(H (1[56] 4[18] 2) )"
      End
      Begin PaneConfiguration = 9
         NumPanes = 2
         Configuration = "(H (1 [75] 4))"
      End
      Begin PaneConfiguration = 10
         NumPanes = 2
         Configuration = "(H (1[66] 2) )"
      End
      Begin PaneConfiguration = 11
         NumPanes = 2
         Configuration = "(H (4 [60] 2))"
      End
      Begin PaneConfiguration = 12
         NumPanes = 1
         Configuration = "(H (1) )"
      End
      Begin PaneConfiguration = 13
         NumPanes = 1
         Configuration = "(V (4))"
      End
      Begin PaneConfiguration = 14
         NumPanes = 1
         Configuration = "(V (2))"
      End
      ActivePaneConfig = 0
   End
   Begin DiagramPane = 
      Begin Origin = 
         Top = 0
         Left = 0
      End
      Begin Tables = 
         Begin Table = "EventLogEntries"
            Begin Extent = 
               Top = 6
               Left = 38
               Bottom = 256
               Right = 198
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "MachineLogs"
            Begin Extent = 
               Top = 6
               Left = 236
               Bottom = 169
               Right = 388
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "EventSources"
            Begin Extent = 
               Top = 6
               Left = 426
               Bottom = 91
               Right = 578
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "UserNames"
            Begin Extent = 
               Top = 206
               Left = 308
               Bottom = 291
               Right = 460
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "EventCategories"
            Begin Extent = 
               Top = 96
               Left = 426
               Bottom = 181
               Right = 578
            End
            DisplayFlags = 280
            TopColumn = 0
         End
      End
   End
   Begin SQLPane = 
   End
   Begin DataPane = 
      Begin ParameterDefaults = ""
      End
      Begin ColumnWidths = 12
         Width = 284
         Width = 1497
         Width = 1497
         Width = 1497
         Width = 1497
         Width = 1497
         Width = 1497
         Width = 1497
         Width = 1497
         Width = 1497
         Width = 1497
         Width = 1497
      End
   End
   Begin Criteria' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'EventLog'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPane2', @value=N'Pane = 
      Begin ColumnWidths = 11
         Column = 1440
         Alias = 2167
         Table = 1169
         Output = 727
         Append = 1400
         NewValue = 1170
         SortType = 1354
         SortOrder = 1411
         GroupBy = 1350
         Filter = 1354
         Or = 1350
         Or = 1350
         Or = 1350
      End
   End
End
' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'EventLog'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPaneCount', @value=2 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'EventLog'
GO
/****** Object:  View [dbo].[EventLogHourlyCounts]    Script Date: 03/11/2010 09:10:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create view [dbo].[EventLogHourlyCounts]
as
SELECT 
	m.MachineLogID, 
	e.EventID,
	Year(e.TimeGenerated) as [Year],
	Month(e.TimeGenerated) as [Month],
	Day(e.TimeGenerated) as [Day],
	datepart(hh, e.TimeGenerated) as [Hour],
	s.SourceID,
	Left(e.EventTypeName, 1) as [EventType],
	Count(e.RecordNumber) as Records
 FROM
	(EventLog e INNER JOIN MachineLogs m 
		on e.ComputerName = m.Machine AND e.EventLog = m.LogName)
	INNER JOIN EventSources s 
		on e.SourceName = s.Source
 GROUP BY 
	m.MachineLogID, 
	e.EventID,
	Year(e.TimeGenerated),
	Month(e.TimeGenerated),
	Day(e.TimeGenerated),
	datepart(hh, e.TimeGenerated),
	s.SourceID,
	Left(e.EventTypeName, 1)
GO
/****** Object:  View [dbo].[EventLogLogMonthlyCounts]    Script Date: 03/11/2010 09:10:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create view [dbo].[EventLogLogMonthlyCounts]
as
select 
	Machine + '\' + LogName as MachineLog,
	cast([Year] as varchar) + '-' +  right('0' + cast([Month] as varchar), 2) as [YearMonth],
	sum(Records) as Records
from 
	dbo.MachineLogs m inner join dbo.EventLogHourlyCounts c on m.MachineLogID = c.MachineLogID
group by 
	Machine + '\' + LogName, 
	cast([Year] as varchar) + '-' +  right('0' + cast([Month] as varchar), 2)
GO
GRANT SELECT ON [dbo].[EventLog] TO [public] AS [dbo]
GO
